﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.IO;
using System.Collections;
using System.Data;

namespace PQ.Util
{
    public class AccessUtil
    {
        public String cons;
        OleDbConnection Con;
        OleDbConnection newCon;
        OleDbDataReader lastReader;
        public AccessUtil(String mdbPath)
        {
            bool isNew = false;
            //如果不存在，就创建
            if (!File.Exists(mdbPath))
            {
                CreateMDBDataBase(mdbPath);
                isNew = true;
            }
            cons = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";";
            Con = new OleDbConnection(cons);
            newCon = new OleDbConnection(cons);
            if (isNew)
            {
                InitialDB();
            }
        }
        //初始化数据，包括一些建表之类的语句
        private void InitialDB()
        {
            String BasicInfo = "Create table [BasicInfo]([id]  AUTOINCREMENT(1,1)  ,[m_name] text ,[m_value] text) ";
            String BasicConfig = "Create table [BasicConfig]([id]  AUTOINCREMENT(1,1)  ,[m_name] text ,[m_value] text) ";
            String HardwareConfig = "Create table [HardwareConfig]([id]  AUTOINCREMENT(1,1),[channelNo] text not null,[measuredValue] text,[factor] float)";
            ExecuteNoQuery(BasicInfo);
            ExecuteNoQuery(BasicConfig);
            ExecuteNoQuery(HardwareConfig);
            for (int i = 1; i < 13; i++)
            {
                ExecuteNoQuery("insert into HardwareConfig(channelNo) values('CH"+i+"')");
            }
            /*
            ExecuteNoQuery("insert into BasicInfo(m_name) values('" + RatingU + "')");
            ExecuteNoQuery("insert into BasicInfo(m_name) values('" + RatingF + "')");
            ExecuteNoQuery("insert into BasicInfo(m_name) values('" + RMSInterval + "')");
            ExecuteNoQuery("insert into BasicInfo(m_name) values('" + HarmonicsInterval + "')");
            ExecuteNoQuery("insert into BasicInfo(m_name) values('" + CustomInterval + "')");
            ExecuteNoQuery("insert into BasicInfo(m_name) values('" + StartTime + "')");
            ExecuteNoQuery("insert into BasicInfo(m_name) values('" + StopTime + "')");
           */
            createBasicPQTable("200ms");
            createBasicPQTable("3s");
            createBasicPQTable("10min");
        }
        // time eg. 200ms ,3s,17min,创建基本电能质量数据库表
        public void createBasicPQTable(String time)
        {
            /*
             * S = | P + jQ |
             * 
             * 
             * UNrms 中线有效值
             * UZrms 零线有效值
             * FA A相频率
             * PA A相有功
             * QA A相无功
             * PFA A相功率因数
             * PFAA A相功角
             * 
             */
            //电能质量数据基本表
            String PQ_Basic = "create table [PQ_Basic" + time + "]([id]  AUTOINCREMENT(1,1),[RecTime] text,[IArms] float,[IBrms] float,[ICrms] float,[INrms] float,[UArms] float,[UBrms] float,[UCrms] float,[UNrms] float,[UZrms] float,[FA] float,[FB] float,[FC] float,[PA] float,[PB] float,[PC] float,[P] float,[QA] float,[QB] float,[QC] float,[PFA] float,[PFB] float,[PFC] float,[PF] float,[SA] float,[SB] float,[SC] float,[S] float,[PFAA] float,[PFAB] float,[PFAC] float)";
            
            //电能质量数据谐波电压表
            String PQ_HarmU = "create table[PQ_HarmU" + time + "]([id]  AUTOINCREMENT(1,1),[RecTime] text,[THDUA] float,[THDUB] float,[THDUC] float,";

            //电能质量数据谐波电流表
            String PQ_HarmI = "create table[PQ_HarmI" + time + "]([id]  AUTOINCREMENT(1,1),[RecTime] text,[THDIA] float,[THDIB] float,[THDIC] float,";

            for (int i = 'A'; i <= 'C'; i++)
            {
                for (int j = 1; j <= 50; j++)
                {
                    PQ_HarmU += "[HRU" + (char)i + j + "] float,";
                    PQ_HarmI += "[HRI" + (char)i + j + "] float,";
                }
            }
            PQ_HarmU = PQ_HarmU.Remove(PQ_HarmU.Length - 1);
            PQ_HarmU += ")";
            PQ_HarmI = PQ_HarmI.Remove(PQ_HarmI.Length - 1);
            PQ_HarmI += ")";

            ExecuteNoQuery(PQ_Basic);
            ExecuteNoQuery(PQ_HarmU);
            ExecuteNoQuery(PQ_HarmI);

            //创建主键及索引
            ExecuteNoQuery("create index PQ_Basic" + time + "_primarykey on PQ_Basic" + time + " (id asc) with PRIMARY ");
            ExecuteNoQuery("create index PQ_HarmU" + time + "_primarykey on PQ_HarmU" + time + " (id asc) with PRIMARY ");
            ExecuteNoQuery("create index PQ_HarmI" + time + "_primarykey on PQ_HarmI" + time + " (id asc) with PRIMARY ");

            ExecuteNoQuery("create unique index PQ_Basic" + time + "_RecTime_Index on PQ_Basic" + time + " (RecTime asc) with DISALLOW NULL");
            ExecuteNoQuery("create unique index PQ_HarmU" + time + "_RecTime_Index on PQ_HarmU" + time + " (RecTime asc) with DISALLOW NULL");
            ExecuteNoQuery("create unique index PQ_HarmI" + time + "_RecTime_Index on PQ_HarmI" + time + " (RecTime asc) with DISALLOW NULL");

            //创建外键关联约束
            ExecuteNoQuery("ALTER TABLE PQ_HarmU" + time + " ADD CONSTRAINT PQ_HarmU" + time + "_Relation FOREIGN KEY ([RecTime]) REFERENCES PQ_Basic" + time + " ([RecTime])");
            ExecuteNoQuery("ALTER TABLE PQ_HarmI" + time + " ADD CONSTRAINT PQ_HarmI" + time + "_Relation FOREIGN KEY ([RecTime]) REFERENCES PQ_Basic" + time + " ([RecTime])");
        }
        //创建mdb   
        public bool CreateMDBDataBase(string mdbPath)
        {
            try
            {
                ADOX.Catalog cat = new ADOX.Catalog();
                cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath + ";");
                cat = null;
                return true;
            }
            catch { return false; }
        }  
  
     public  OleDbDataReader NewExecuteReader(String sql)
    {
         if (lastReader !=null)
         {
             if (!lastReader.IsClosed)
             {
                 lastReader.Close();
             }
         }
        newCon.Open();
        OleDbCommand Cmd = new OleDbCommand(sql,newCon);
          lastReader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
          return lastReader;
         
    }
    public  OleDbDataReader ExecuteReader(String sql)
    {
      
        OleDbCommand Cmd = new OleDbCommand(sql,Con);
          return Cmd.ExecuteReader();
         
    }
    public void ExecuteNoQuery(String sql)
    {
        Con.Open();
        OleDbCommand Cmd = new OleDbCommand(sql, Con);
         Cmd.ExecuteNonQuery();
         Con.Close();
    }
        public Configuration loadConfiguration()
        {
            Con.Open();
            Configuration cfg = new Configuration();
            OleDbDataReader reader = ExecuteReader("select m_name,m_value from BasicConfig");
            while (reader.Read())
            {
                cfg[reader.GetString(0)] = reader.GetString(1);
            }
            reader = ExecuteReader("select m_name,m_value from BasicInfo");
            while (reader.Read())
            {
                cfg.addBasicInfo(reader.GetString(0), reader.GetString(1));
            }
            reader = ExecuteReader("select channelNo,measuredValue,factor from HardwareConfig");
            while (reader.Read())
            {
                cfg.setHardwareConfig(reader.GetString(0), reader.GetString(1),reader.GetDouble(2));
            }
            Con.Close();
            return cfg;
        }
        public void saveConfiguration(Configuration cfg)
        {
           

            ExecuteNoQuery("delete from BasicInfo");
            ExecuteNoQuery("delete from BasicConfig");
            ExecuteNoQuery("delete from HardwareConfig");
            Hashtable hashtable = cfg.cfgHashtable;
            foreach(DictionaryEntry de in hashtable)
            {
                if(de.Key.ToString() != "")
                    ExecuteNoQuery("insert into BasicConfig(m_name,m_value) values('" + de.Key + "','" + de.Value + "')");

            }
            for(int i = 0; i < cfg.getBasicInfoSize();i++)
            {
                if(cfg.getBasicInfoName(i) != "")
                ExecuteNoQuery("insert into BasicInfo(m_name,m_value) values('" +cfg.getBasicInfoName(i) + "','" + cfg.getBasicInfoValue(i) + "')");
            }
            ExecuteNoQuery("insert into HardwareConfig(channelNo,measuredValue,factor) values('" + cfg.chArr[cfg.indexUA] + "','UA'," + cfg.Fua + ")");
            ExecuteNoQuery("insert into HardwareConfig(channelNo,measuredValue,factor) values('" + cfg.chArr[cfg.indexUB] + "','UB'," + cfg.Fub + ")");
            ExecuteNoQuery("insert into HardwareConfig(channelNo,measuredValue,factor) values('" + cfg.chArr[cfg.indexUC] + "','UC'," + cfg.Fuc + ")");
            ExecuteNoQuery("insert into HardwareConfig(channelNo,measuredValue,factor) values('" + cfg.chArr[cfg.indexUN] + "','UN'," + cfg.Fun + ")");
            ExecuteNoQuery("insert into HardwareConfig(channelNo,measuredValue,factor) values('" + cfg.chArr[cfg.indexUZ] + "','UZ'," + cfg.Fuz + ")");
            ExecuteNoQuery("insert into HardwareConfig(channelNo,measuredValue,factor) values('" + cfg.chArr[cfg.indexIA] + "','IA'," + cfg.Fia + ")");
            ExecuteNoQuery("insert into HardwareConfig(channelNo,measuredValue,factor) values('" + cfg.chArr[cfg.indexIB] + "','IB'," + cfg.Fib + ")");
            ExecuteNoQuery("insert into HardwareConfig(channelNo,measuredValue,factor) values('" + cfg.chArr[cfg.indexIC] + "','IC'," + cfg.Fic + ")");
            ExecuteNoQuery("insert into HardwareConfig(channelNo,measuredValue,factor) values('" + cfg.chArr[cfg.indexIN] + "','IN'," + cfg.Fin + ")");

        }
    }
}
